<?php
namespace Operationtest\Model;

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\AbstractTableGateway;

class SaleheadTable extends AbstractTableGateway
{
    protected $table ='saleHead';
    
    public function __construct(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Salehead());
        $this->initialize();
    }
    
    public function getNbOpenDays($where) {
        $sql = "SELECT 
                    orderDate,
                    COUNT(DISTINCT(orderDate)) as nbOpenDays
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND (workerMeal = 0 OR workerMeal is NULL)
                GROUP BY orderDate";
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;
        
        return $rows;
    }
    
    public function getTbCa($where) {
        $sql = "SELECT 
                    SUM(totttc) AS ca_ttc,
                    SUM(totht) AS ca_ht,
                    orderDate
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND (workerMeal = 0 OR workerMeal is NULL)";
        //echo $sql.'<br />';
        $result = $this->adapter->query($sql)->execute();
        return $result->current();
    }
    
    public function getTbTmHt($where) {
        $sql = "SELECT 
                    SUM(totht) AS valeur,
                    COUNT(idSaleHead) AS nombre
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND (workerMeal = 0 OR workerMeal is NULL)";
        
        $result = $this->adapter->query($sql)->execute();
        $return = $result->current();
        
        return $return;
    }
    
    public function getTbTmTtc($where) {
        $sql = "SELECT 
                    SUM(totttc) AS valeur,
                    COUNT(idSaleHead) AS nombre
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND (workerMeal = 0 OR workerMeal is NULL)";
        
        $result = $this->adapter->query($sql)->execute();
        $return = $result->current();
        
        return $return;
    }
    
    public function getTbTransaction($where) {
        $sql = "SELECT 
                    COUNT(*) AS nbTransaction
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND (workerMeal = 0 OR workerMeal is NULL)";
        
        $result = $this->adapter->query($sql)->execute();
        $return = $result->current();

        return $return;
    }
    
    public function getTbRepas($where) {
        $sql = "SELECT 
                    SUM(workerMeal) AS repas,
                    orderDate
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND workerMeal = 1";
        //echo $sql.'<br />';
        $result = $this->adapter->query($sql)->execute();
        $return = $result->current();

        return $return;
    }
    
    public function getTbAnnulationCaisse($where) {
        $sql = "SELECT 
                    SUM(totttc) AS montant_ac,
                    COUNT(*) AS ac
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 1";
        
        $result = $this->adapter->query($sql)->execute();
        $return = $result->current();

        return $return;
    }
        
    public function getSynthCa($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    SUM(totttc) AS ca_ttc,
                    SUM(totht) AS ca_ht,
                    idStore,
                    orderDate
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby;
        
        //echo $sql.'<br />';
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;
        
        return $rows;
    }
    
    public function getSynthTmHt($where, $groupby="") {
        $sql = "SELECT 
                    SUM(totht) AS valeur,
                    COUNT(idSaleHead) AS nombre,
                    orderDate
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getSynthTmTtc($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    SUM(totttc) AS valeur,
                    COUNT(idSaleHead) AS nombre,
                    orderDate
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getSynthVentilationTva($where, $groupby="") {
        $rows = array();
        $sql = "SELECT SUM(saleRow.totalPriceTTC) AS ca_ttc, saleRow.taxeRate, orderDate "
                . "FROM ".$this->table." "
                . "JOIN saleRow ON (saleHead.idSaleHead = saleRow.idsaleHead) "
                . "WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)"
                . $groupby;
//        echo $sql;
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;
        return $rows;
    }
    
    public function getSynthTransaction($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    COUNT(*) AS nbTransaction,
                    saleHead.idStore,
                    saleHead.orderDate
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getSynthAnnulationCaisse($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    SUM(totttc) AS montant_ac,
                    COUNT(*) AS ac,
                    saleHead.idStore,
                    saleHead.orderDate
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 1
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getSynthRepas($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    SUM(workerMeal) AS repas,
                    idStore,
                    orderDate
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND workerMeal = 1 ".$groupby;
        //echo $sql.'<br />';
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getBgCa($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    SUM(totttc) AS ca_ttc,
                    SUM(totht) AS ca_ht,
                    orderDate,
                    idStore
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby;
        //echo $sql;
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;
        
        return $rows;
    }
    
    public function getBgTmTtc($where, $groupby="") {
        $sql = "SELECT 
                    SUM(totttc) AS valeur,
                    COUNT(idSaleHead) AS nombre,
                    orderDate,
                    idStore
                FROM ".$this->table."
                WHERE ".$where." AND canceled = 0 AND (workerMeal = 0 OR workerMeal is NULL)
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getBgTransaction($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    COUNT(*) AS nbTransaction,
                    saleHead.orderDate,
                    saleHead.idStore
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getBgAnnulation($where, $groupby="") {
        $rows = array();
        $sql = "SELECT 
                    COUNT(*) AS nbAnnulation,
                    saleHead.orderDate,
                    saleHead.idStore
                FROM ".$this->table."
                WHERE ".$where." AND saleHead.canceled = 1
                ".$groupby;
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getBgMarcheCa($where) {
        $rows = array();
        $sql = "SELECT 
                    SUM(totttc) AS ca_ttc,
                    SUM(totht) AS ca_ht,
                    idMarche
                FROM ".$this->table."
                JOIN MarcheStore
                    ON (MarcheStore.idStore = saleHead.idStore)
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                GROUP BY idMarche";
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;
        
        return $rows;
    }
    
    public function getBgMarcheTmTtc($where) {
        $sql = "SELECT 
                    SUM(totttc) AS valeur,
                    COUNT(idSaleHead) AS nombre,
                    idMarche
                FROM ".$this->table."
                JOIN MarcheStore
                    ON (MarcheStore.idStore = saleHead.idStore)
                WHERE ".$where." AND canceled = 0 AND (workerMeal = 0 OR workerMeal is NULL)
                GROUP BY idMarche";

        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getBgMarcheTransaction($where) {
        $rows = array();
        $sql = "SELECT 
                    COUNT(*) AS nbTransaction,
                    idMarche
                FROM ".$this->table."
                JOIN MarcheStore
                    ON (MarcheStore.idStore = saleHead.idStore)
                WHERE ".$where." AND saleHead.canceled = 0 AND (saleHead.workerMeal = 0 OR saleHead.workerMeal is NULL)
                GROUP BY idMarche";
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
    
    public function getBgMarcheAnnulation($where) {
        $rows = array();
        $sql = "SELECT 
                    COUNT(*) AS nbAnnulation,
                    idMarche
                FROM ".$this->table."
                JOIN MarcheStore
                    ON (MarcheStore.idStore = saleHead.idStore)
                WHERE ".$where." AND saleHead.canceled = 1
                GROUP BY idMarche";
        
        $result = $this->adapter->query($sql)->execute();
        for($x=0;$x<count($result);$x++):
            $rows[$x] = $result->current();
        endfor;

        return $rows;
    }
}
